import os
import pymysql
from datetime import datetime, timedelta
from dotenv import load_dotenv

# 加载环境变量
load_dotenv()

# MySQL连接配置 - 使用环境变量
MYSQL_CONFIG = {
    'host': os.environ.get('DB_HOST', 'localhost'),
    'user': os.environ.get('DB_USER', 'root'),
    'password': os.environ.get('DB_PASSWORD'),
    'database': os.environ.get('DB_NAME', 'basal_adjust'),
    'port': int(os.environ.get('DB_PORT', '3306')),
    'charset': 'utf8mb4'
}

# 测试患者编号
PATIENT_NUMBER = os.environ.get('TEST_PATIENT_NUMBER', 'test001')

def get_data_by_hour():
    """按小时查询血糖数据"""
    try:
        # 检查数据库密码是否配置
        if not MYSQL_CONFIG['password']:
            print("错误：数据库密码未配置。请在.env文件中设置DB_PASSWORD")
            return {}
            
        conn = pymysql.connect(**MYSQL_CONFIG)
        cursor = conn.cursor()
        
        # 准备24小时的数据
        hours_data = {}
        for hour in range(24):
            start_time = f"2025-05-20 {hour:02d}:00:00"
            end_time = f"2025-05-20 {hour:02d}:59:59"
            
            query = f"""
            SELECT sg_date, sgv 
            FROM cgm_{PATIENT_NUMBER} 
            WHERE sg_date BETWEEN %s AND %s 
            ORDER BY sg_date
            """
            
            cursor.execute(query, (start_time, end_time))
            results = cursor.fetchall()
            
            # 存储结果
            if results:
                hours_data[hour] = results
        
        cursor.close()
        conn.close()
        return hours_data
    except Exception as e:
        print(f"查询数据失败: {e}")
        return {}

def analyze_glucose_trends():
    """分析血糖趋势"""
    try:
        # 检查数据库密码是否配置
        if not MYSQL_CONFIG['password']:
            print("错误：数据库密码未配置。请在.env文件中设置DB_PASSWORD")
            return
            
        conn = pymysql.connect(**MYSQL_CONFIG)
        cursor = conn.cursor()
        
        # 查询一天的数据
        query = f"""
        SELECT sg_date, sgv 
        FROM cgm_{PATIENT_NUMBER} 
        WHERE DATE(sg_date) = '2025-05-20'
        ORDER BY sg_date
        """
        
        cursor.execute(query)
        results = cursor.fetchall()
        
        if not results:
            print(f"患者 {PATIENT_NUMBER} 没有找到数据")
            return
        
        print(f"找到 {len(results)} 条数据")
        
        # 计算统计信息
        sgv_values = [row[1] for row in results]
        avg_sgv = sum(sgv_values) / len(sgv_values)
        max_sgv = max(sgv_values)
        min_sgv = min(sgv_values)
        
        print(f"血糖统计:")
        print(f"  平均值: {avg_sgv:.1f} mg/dL")
        print(f"  最高值: {max_sgv} mg/dL")
        print(f"  最低值: {min_sgv} mg/dL")
        
        # 计算目标范围内的时间百分比 (70-180 mg/dL)
        in_range = len([sgv for sgv in sgv_values if 70 <= sgv <= 180])
        time_in_range = (in_range / len(sgv_values)) * 100
        print(f"  目标范围内时间: {time_in_range:.1f}%")
        
        cursor.close()
        conn.close()
        
    except Exception as e:
        print(f"分析血糖趋势失败: {e}")

def get_meal_time_data():
    """获取餐时血糖数据"""
    try:
        # 检查数据库密码是否配置
        if not MYSQL_CONFIG['password']:
            print("错误：数据库密码未配置。请在.env文件中设置DB_PASSWORD")
            return
            
        conn = pymysql.connect(**MYSQL_CONFIG)
        cursor = conn.cursor()
        
        # 定义餐时时间段
        meal_times = {
            '早餐前': ('07:00:00', '08:00:00'),
            '早餐后': ('08:00:00', '10:00:00'),
            '午餐前': ('11:00:00', '12:00:00'),
            '午餐后': ('12:00:00', '14:00:00'),
            '晚餐前': ('17:00:00', '18:00:00'),
            '晚餐后': ('18:00:00', '20:00:00'),
        }
        
        print("\n餐时血糖数据:")
        for meal_name, (start_time, end_time) in meal_times.items():
            query = f"""
            SELECT AVG(sgv) as avg_sgv, COUNT(*) as count
            FROM cgm_{PATIENT_NUMBER} 
            WHERE DATE(sg_date) = '2025-05-20'
            AND TIME(sg_date) BETWEEN %s AND %s
            """
            
            cursor.execute(query, (start_time, end_time))
            result = cursor.fetchone()
            
            if result and result[0]:
                print(f"  {meal_name}: {result[0]:.1f} mg/dL (共{result[1]}个数据点)")
            else:
                print(f"  {meal_name}: 无数据")
        
        cursor.close()
        conn.close()
        
    except Exception as e:
        print(f"获取餐时数据失败: {e}")

def check_data_completeness():
    """检查数据完整性"""
    try:
        # 检查数据库密码是否配置
        if not MYSQL_CONFIG['password']:
            print("错误：数据库密码未配置。请在.env文件中设置DB_PASSWORD")
            return
            
        conn = pymysql.connect(**MYSQL_CONFIG)
        cursor = conn.cursor()
        
        # 检查表是否存在
        cursor.execute(f"SHOW TABLES LIKE 'cgm_{PATIENT_NUMBER}'")
        table_exists = cursor.fetchone()
        
        if not table_exists:
            print(f"表 cgm_{PATIENT_NUMBER} 不存在")
            return
        
        # 检查数据范围
        query = f"""
        SELECT 
            MIN(sg_date) as earliest,
            MAX(sg_date) as latest,
            COUNT(*) as total_records
        FROM cgm_{PATIENT_NUMBER}
        """
        
        cursor.execute(query)
        result = cursor.fetchone()
        
        if result:
            print(f"\n数据完整性检查:")
            print(f"  最早时间: {result[0]}")
            print(f"  最晚时间: {result[1]}")
            print(f"  总记录数: {result[2]}")
            
            # 检查数据间隔
            query = f"""
            SELECT sg_date 
            FROM cgm_{PATIENT_NUMBER} 
            ORDER BY sg_date 
            LIMIT 10
            """
            cursor.execute(query)
            time_samples = cursor.fetchall()
            
            if len(time_samples) >= 2:
                first_interval = time_samples[1][0] - time_samples[0][0]
                print(f"  数据间隔: {first_interval}")
        
        cursor.close()
        conn.close()
        
    except Exception as e:
        print(f"检查数据完整性失败: {e}")

def main():
    """主函数"""
    print(f"CGM数据验证工具")
    print(f"患者编号: {PATIENT_NUMBER}")
    print("=" * 50)
    
    # 检查环境变量配置
    if not MYSQL_CONFIG['password']:
        print("错误：数据库密码未配置")
        print("请执行以下步骤：")
        print("1. 复制 env.example 为 .env")
        print("2. 在 .env 文件中设置 DB_PASSWORD=你的数据库密码")
        return
    
    print(f"连接到数据库: {MYSQL_CONFIG['host']}:{MYSQL_CONFIG['port']}/{MYSQL_CONFIG['database']}")
    
    # 检查数据完整性
    check_data_completeness()
    
    # 分析血糖趋势
    analyze_glucose_trends()
    
    # 获取餐时数据
    get_meal_time_data()
    
    print("\n验证完成!")

if __name__ == "__main__":
    main() 